﻿using Dapper;
using IRepository;
using Entity;
using System;
using System.Data;
using System.Data.Common; // 引入DbConnection所在命名空间
using System.Threading.Tasks;
using System.Collections.Generic;
using Microsoft.Extensions.Logging;
using System.Text;
using Microsoft.AspNetCore.Http;
using Newtonsoft.Json;

namespace Repository
{
    public class StorageLocationRepository : IStorageLocationRepository
    {
        // 修正1：将具体类型SqlConnection改为抽象类型DbConnection
        private readonly DbConnection _dbConnection;
        private readonly ILogger<StorageLocationRepository> _logger;
        private readonly IHttpContextAccessor _httpContextAccessor;
        // 修正2：提供IDbConnection接口暴露（兼容需要此接口的场景）
        public IDbConnection GetDbConnection()
        {
            return _dbConnection; // DbConnection实现了IDbConnection，直接返回
        }

        // 修正3：构造函数参数类型改为DbConnection，与依赖注入类型匹配
        public StorageLocationRepository(DbConnection dbConnection, ILogger<StorageLocationRepository> logger, IHttpContextAccessor httpContextAccessor)
        {
            _dbConnection = dbConnection ?? throw new ArgumentNullException(nameof(dbConnection));
            _logger = logger ?? throw new ArgumentNullException(nameof(logger));
            _httpContextAccessor = httpContextAccessor;
        }

        public async Task<PaginationResult<StorageLocationEntity>> GetPagedListAsync(PaginationQuery query)
        {
            if (_dbConnection.State != ConnectionState.Open)
            {
                await _dbConnection.OpenAsync(); // DbConnection支持异步打开
            }

            // 明确指定is_delete属于库位表（sl.is_delete）
            var whereClause = "WHERE sl.is_delete = 0";

            // 关联用户表时，过滤有效用户
            var dataSql = $@"
        SELECT sl.*, u.user_id as users_id
        FROM wms_basic_storage_location sl
        LEFT JOIN sys_org_user_management u 
            ON sl.users_id = u.user_id 
            AND u.is_delete = 0  -- 仅关联未删除的用户
        {whereClause}
        ORDER BY sl.location_id
        OFFSET @SkipCount ROWS FETCH NEXT @PageSize ROWS ONLY";

            var data = (await _dbConnection.QueryAsync<StorageLocationEntity>(dataSql, new
            {
                SkipCount = query.SkipCount,
                PageSize = query.PageSize
            })).AsList();

            // 总数查询明确表别名
            var countSql = $"SELECT COUNT(*) FROM wms_basic_storage_location sl {whereClause}";
            var totalCount = await _dbConnection.ExecuteScalarAsync<int>(countSql);

            return new PaginationResult<StorageLocationEntity>
            {
                Data = data,
                TotalCount = totalCount,
                PageIndex = query.PageIndex,
                PageSize = query.PageSize
            };
        }

        public async Task DeleteAsync(int id)
        {
            _logger.LogInformation("开始删除库位，ID：{LocationId}", id);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }
                var sql = "UPDATE wms_basic_storage_location SET is_delete = 1 WHERE location_id = @Id";
                var affectedRows = await _dbConnection.ExecuteAsync(sql, new { Id = id });
                if (affectedRows == 0)
                {
                    _logger.LogWarning("删除库位失败，未找到ID为{LocationId}的库位", id);
                    throw new KeyNotFoundException($"未找到ID为{id}的库位");
                }
                _logger.LogInformation("库位删除成功，ID：{LocationId}", id);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "删除库位异常，ID：{LocationId}", id);
                throw;
            }
        }

        public async Task DeleteBatchAsync(int[] ids)
        {
            _logger.LogInformation("开始批量删除库位，ID列表：{Ids}", string.Join(",", ids));
            try
            {
                if (ids == null || ids.Length == 0)
                {
                    _logger.LogWarning("批量删除库位失败，ID列表为空");
                    throw new ArgumentException("批量删除的库位ID列表不能为空");
                }

                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }
                var sql = "UPDATE wms_basic_storage_location SET is_delete = 1 WHERE location_id IN @Ids";
                var affectedRows = await _dbConnection.ExecuteAsync(sql, new { Ids = ids });
                _logger.LogInformation("批量删除库位完成，影响行数：{AffectedRows}，ID列表：{Ids}",
                    affectedRows, string.Join(",", ids));
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "批量删除库位异常，ID列表：{Ids}", string.Join(",", ids));
                throw;
            }
        }

        public async Task<int> AddAsync(StorageLocationEntity entity)
        {
            _logger.LogInformation("开始新增库位，编号：{LocationNumber}", entity.location_number);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                // 校验库位编号是否已存在
                var existsSql = "SELECT COUNT(1) FROM wms_basic_storage_location WHERE location_number = @Number AND is_delete = 0";
                var exists = await _dbConnection.ExecuteScalarAsync<bool>(existsSql, new { Number = entity.location_number });
                if (exists)
                {
                    throw new InvalidOperationException($"库位编号{entity.location_number}已存在");
                }

                // 明确设置 warehouse_id 为 0（未关联仓库）
                entity.warehouse_id = 0;

                // 插入库位数据
                var sql = @"
            INSERT INTO wms_basic_storage_location 
            (warehouse_id, location_number, location_name, storage_location_capacity, users_id, 
             sorting, location_status, note, create_by, create_time, update_by, update_time, is_delete)
            VALUES 
            (@warehouse_id, @location_number, @location_name, @storage_location_capacity, @users_id, 
             @sorting, @location_status, @note, @create_by, @create_time, @update_by, @update_time, @is_delete);
            SELECT CAST(SCOPE_IDENTITY() AS INT);";

                var newId = await _dbConnection.QueryFirstAsync<int>(sql, entity);
                _logger.LogInformation("库位新增成功，ID：{NewId}，编号：{LocationNumber}", newId, entity.location_number);
                return newId;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "新增库位失败，编号：{LocationNumber}", entity.location_number);
                throw;
            }
        }
        // 新增：实现根据仓库ID获取库位ID列表
        public async Task<List<int>> GetIdsByWarehouseIdAsync(int warehouseId)
        {
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                // 查询条件：关联指定仓库ID，且库位未删除（is_delete=0）
                var sql = @"
                    SELECT location_id 
                    FROM wms_basic_storage_location 
                    WHERE warehouse_id = @WarehouseId 
                      AND is_delete = 0";

                var locationIds = (await _dbConnection.QueryAsync<int>(sql, new { WarehouseId = warehouseId }))
                    .ToList();

                return locationIds;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询仓库关联的库位ID失败，仓库ID：{WarehouseId}", warehouseId);
                throw; // 抛出异常让上层处理
            }
        }
        public async Task<List<StorageLocationEntity>> GetByWarehouseIdAsync(int warehouseId)
        {
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                // 关键修正：去掉字段别名，直接使用数据库字段名（与实体类下划线属性匹配）
                var sql = @"
        SELECT 
            location_id,  -- 对应实体类的 location_id 属性
            location_name,  -- 对应实体类的 location_name 属性
            location_number,  -- 对应实体类的 location_number 属性
            warehouse_id,  -- 可选：如需返回仓库ID，可包含
            is_delete  -- 可选：如需验证删除状态，可包含
        FROM wms_basic_storage_location 
        WHERE 
            warehouse_id = @WarehouseId  -- 过滤指定仓库
            AND is_delete = 0  -- 排除已删除的库位
        ORDER BY location_name";  // 按名称排序

                // Dapper会自动将查询结果（字段名：location_id）映射到实体类的 location_id 属性
                var locations = (await _dbConnection.QueryAsync<StorageLocationEntity>(
                    sql,
                    new { WarehouseId = warehouseId }  // 参数匹配
                )).AsList();

                return locations;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据仓库ID查询仓库位失败，仓库ID：{WarehouseId}", warehouseId);
                throw;
            }
        }
        /// <summary>
        /// 实现带仓库ID过滤的库位分页查询
        /// </summary>
        public async Task<PaginationResult<StorageLocationEntity>> GetPagedListAsync(int? warehouseId, PaginationQuery pagination)
        {
            try
            {
                _logger.LogInformation("【库位仓储】分页查询库位，warehouseId：{WarehouseId}，页码：{PageIndex}，页大小：{PageSize}",
                    warehouseId, pagination.PageIndex, pagination.PageSize);

                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                var pageIndex = pagination.PageIndex < 1 ? 1 : pagination.PageIndex;
                var pageSize = pagination.PageSize < 1 || pagination.PageSize > 100 ? 10 : pagination.PageSize;
                var skipCount = (pageIndex - 1) * pageSize;

                var whereConditions = new List<string>();
                var parameters = new DynamicParameters();
                parameters.Add("@SkipCount", skipCount);
                parameters.Add("@PageSize", pageSize);
                parameters.Add("@IsDelete", "0"); // 未删除
                parameters.Add("@LocationStatus", "1"); // 启用状态

                // 核心修正：未关联仓库时 warehouse_id = 0
                if (warehouseId == null)
                {
                    whereConditions.Add("warehouse_id = 0");
                }
                else
                {
                    whereConditions.Add("warehouse_id = @WarehouseId");
                    parameters.Add("@WarehouseId", warehouseId.Value);
                }

                // 拼接基础条件
                whereConditions.Add("is_delete = @IsDelete");
                whereConditions.Add("location_status = @LocationStatus");
                var whereSql = whereConditions.Any() ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                // 统计总数
                var countSql = $"SELECT COUNT(1) FROM wms_basic_storage_location {whereSql}";
                var totalCount = await _dbConnection.QueryFirstOrDefaultAsync<int>(countSql, parameters);

                // 查询分页数据
                var dataSql = $@"
            SELECT 
                location_id,
                location_number,
                location_name,
                storage_location_capacity,
                users_id,
                sorting,
                location_status,
                note,
                warehouse_id
            FROM wms_basic_storage_location
            {whereSql}
            ORDER BY sorting ASC, location_name ASC
            OFFSET @SkipCount ROWS FETCH NEXT @PageSize ROWS ONLY
        ";

                var data = await _dbConnection.QueryAsync<StorageLocationEntity>(dataSql, parameters);
                var dataList = data?.ToList() ?? new List<StorageLocationEntity>();

                return new PaginationResult<StorageLocationEntity>
                {
                    Data = dataList,
                    TotalCount = totalCount,
                    PageIndex = pageIndex,
                    PageSize = pageSize
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "【库位仓储】分页查询库位异常");
                throw;
            }
        }
        public async Task<int> AddAsync(StorageLocationAddDTO dto, int warehouseId = 0)
        {
            _logger.LogInformation("开始新增库位，编号：{LocationNumber}，关联仓库ID：{WarehouseId}", dto.location_number, warehouseId);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                // 校验库位编号唯一性
                var existsSql = "SELECT COUNT(1) FROM wms_basic_storage_location WHERE location_number = @Number AND is_delete = 0";
                var exists = await _dbConnection.ExecuteScalarAsync<bool>(existsSql, new { Number = dto.location_number });
                if (exists)
                {
                    throw new InvalidOperationException($"库位编号{dto.location_number}已存在");
                }

                // 构造库位实体并关联仓库ID
                var entity = new StorageLocationEntity
                {
                    warehouse_id = warehouseId, // 关键：关联仓库ID
                    location_number = dto.location_number,
                    location_name = dto.location_name,
                    storage_location_capacity = dto.storage_location_capacity,
                    users_id = dto.users_id,
                    sorting = dto.sorting,
                    location_status = dto.location_status,
                    note = dto.note ?? string.Empty,
                    create_by = GetCurrentUserName(),
                    create_time = DateTime.Now,
                    update_by = GetCurrentUserName(),
                    update_time = DateTime.Now,
                    is_delete = 0
                };

                // 插入库位数据
                var insertSql = @"
INSERT INTO wms_basic_storage_location 
(warehouse_id, location_number, location_name, storage_location_capacity, users_id, 
 sorting, location_status, note, create_by, create_time, update_by, update_time, is_delete)
VALUES 
(@warehouse_id, @location_number, @location_name, @storage_location_capacity, @users_id, 
 @sorting, @location_status, @note, @create_by, @create_time, @update_by, @update_time, @is_delete);
SELECT CAST(SCOPE_IDENTITY() AS INT);";

                var newId = await _dbConnection.QueryFirstAsync<int>(insertSql, entity);
                _logger.LogInformation("库位新增成功，ID：{NewId}，编号：{LocationNumber}", newId, dto.location_number);
                return newId;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "新增库位失败，编号：{LocationNumber}，关联仓库ID：{WarehouseId}", dto.location_number, warehouseId);
                throw;
            }
            // 移除finally中的CloseAsync，由上层事务统一管理连接
        }

        private string GetCurrentUserName()
        {
            if (_httpContextAccessor?.HttpContext == null)
            {
                _logger.LogWarning("HttpContext为空，使用默认用户: system");
                return "system";
            }

            var session = _httpContextAccessor.HttpContext.Session;
            if (session.TryGetValue("LoginUserName", out var userBytes))
            {
                return Encoding.UTF8.GetString(userBytes);
            }

            return _httpContextAccessor.HttpContext.User.Identity.Name ?? "system";
        }
        // 新增：校验库位编号是否已存在（未删除的库位）
        public async Task<bool> ExistsByNumberAsync(string locationNumber)
        {
            if (string.IsNullOrWhiteSpace(locationNumber))
                return false;

            if (_dbConnection.State != ConnectionState.Open)
                await _dbConnection.OpenAsync();

            var sql = "SELECT COUNT(1) FROM wms_basic_storage_location WHERE location_number = @Number AND is_delete = 0";
            var count = await _dbConnection.ExecuteScalarAsync<int>(sql, new { Number = locationNumber });
            return count > 0;
        }
        public async Task<int> AddAsync(StorageLocationAddDTO dto, int warehouseId, IDbTransaction transaction)
        {
            _logger.LogDebug("【库位新增-事务版】开始处理，编号：{LocationNumber}，仓库ID：{WarehouseId}", dto.location_number, warehouseId);
            try
            {
                // 复用原有校验逻辑，但不单独打开连接（使用事务的连接）
                var existsSql = "SELECT COUNT(1) FROM wms_basic_storage_location WHERE location_number = @Number AND is_delete = 0";
                var exists = await _dbConnection.ExecuteScalarAsync<bool>(existsSql, new { Number = dto.location_number }, transaction);
                if (exists)
                {
                    _logger.LogError("【库位新增-事务版】编号重复：{LocationNumber}", dto.location_number);
                    throw new InvalidOperationException($"库位编号{dto.location_number}已存在");
                }

                var entity = new StorageLocationEntity
                {
                    warehouse_id = warehouseId,
                    location_number = dto.location_number,
                    location_name = dto.location_name,
                    storage_location_capacity = dto.storage_location_capacity,
                    users_id = dto.users_id,
                    sorting = dto.sorting,
                    location_status = dto.location_status,
                    note = dto.note ?? string.Empty,
                    create_by = GetCurrentUserName(),
                    create_time = DateTime.Now,
                    update_by = GetCurrentUserName(),
                    update_time = DateTime.Now,
                    is_delete = 0
                };

                var insertSql = @"
INSERT INTO wms_basic_storage_location 
(warehouse_id, location_number, location_name, storage_location_capacity, users_id, 
 sorting, location_status, note, create_by, create_time, update_by, update_time, is_delete)
VALUES 
(@warehouse_id, @location_number, @location_name, @storage_location_capacity, @users_id, 
 @sorting, @location_status, @note, @create_by, @create_time, @update_by, @update_time, @is_delete);
SELECT CAST(SCOPE_IDENTITY() AS INT);";

                _logger.LogDebug("【库位新增-事务版】执行SQL：{Sql}，参数：{Entity}", insertSql, JsonConvert.SerializeObject(entity));
                var newId = await _dbConnection.QueryFirstAsync<int>(insertSql, entity, transaction);
                _logger.LogInformation("【库位新增-事务版】成功，ID：{NewId}，编号：{LocationNumber}", newId, dto.location_number);
                return newId;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "【库位新增-事务版】失败，编号：{LocationNumber}，仓库ID：{WarehouseId}", dto.location_number, warehouseId);
                throw;
            }
        }
    }
}